package com.e1xp.deport_management_system.mapper;

import com.e1xp.deport_management_system.utils.AnalyseData;
import com.e1xp.deport_management_system.utils.ProductDataWrapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * @Author E1xP@foxmail.com
 * @Version 1.0
 * @PACKAGE_NAME com.e1xp.deport_management_system.mapper
 * @CLASS_NAME AnalyseMapper
 * @Description TODO 数据分析Mapper
 * @Date 2021/5/17 下午 1:27
 **/
@Mapper
public interface AnalyseMapper {

    @Select("SELECT date_format(`time`, '%m-%d') as time, " +
            "sum(io.count) as count " +
            "FROM io " +
            "WHERE instance_id=#{instanceId} and type=0 and " +
            "time BETWEEN #{startTime} and #{endTime} " +
            "GROUP BY date_format(`time`, '%m-%d')")
    List<AnalyseData> getInCount(@Param("instanceId") long instanceId, @Param("startTime") LocalDate startTime, @Param("endTime") LocalDate endTime);

    @Select("SELECT date_format(`time`,'%m-%d') as time, " +
            "sum(io.count) as count " +
            "FROM io " +
            "WHERE instance_id=#{instanceId} and type=1 and " +
            "time BETWEEN #{startTime} and #{endTime} " +
            "GROUP BY date_format(`time`, '%m-%d')")
    List<AnalyseData> getOutCount(@Param("instanceId") long instanceId, @Param("startTime") LocalDate startTime, @Param("endTime") LocalDate endTime);

    @Select("SELECT date_format(order_time, '%m-%d') as time, " +
            "count(*) as count " +
            "FROM `order` " +
            "WHERE instance_id=#{instanceId} and " +
            "order_time BETWEEN #{startTime} and #{endTime} " +
            "GROUP BY date_format(order_time, '%m-%d')")
    List<AnalyseData> getOrderCount(@Param("instanceId") long instanceId, @Param("startTime") LocalDate startTime, @Param("endTime") LocalDate endTime);

    @Select("SELECT p.name as name," +
            "sum(op.count) as count " +
            "FROM `order` as o " +
            "left join order_product op on o.id = op.order_id " +
            "left join product p on p.id = op.product_id " +
            "WHERE o.instance_id=#{instanceId} and " +
            "order_time BETWEEN #{startTime} and #{endTime} " +
            "GROUP BY p.name " +
            "ORDER By sum(op.count) DESC " +
            "LIMIT 8")
    List<ProductDataWrapper> getProductSellCount(@Param("instanceId") long instanceId, @Param("startTime") LocalDate startTime, @Param("endTime") LocalDate endTime);
}
